﻿
Partial Class WebForm_ManageSalesForecast_Stock
    Inherits System.Web.UI.Page
    Dim cmanager As New ChannelManager

    Protected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load
        If Not IsPostBack Then
            If Request.QueryString("SapCode") IsNot Nothing Then
                Me.SapCode = Request.QueryString.Item("SapCode").ToString().Replace(MyObj.GetAreaCode(), "")
                Me.BindData()
            End If
            If Request.QueryString("Stock") IsNot Nothing Then
                Me.btnCancel.Visible = False
            End If
        End If
    End Sub

    Private Sub BindData()
        Dim sql As New StringBuilder
        Dim dat As New clsData
        Dim dt As DataTable

        Dim SkuType As String = ""
        Select Case Me.HeaderTB
            Case "SO_"
                SkuType = "SellOut"
            Case "PLV_"
                SkuType = "S"
            Case Else
                SkuType = "S"
        End Select

        sql.Length = 0
        sql.AppendFormat("select a.*,isnull(cast(c.TradeStock as varchar),'NA') as TradeStock ")
        sql.AppendFormat(",isnull(cast(STOCK_PBOM.TradeStock as varchar),'NA') as TradeStock_PBOM ")
        sql.AppendFormat(",isnull(b.WHStock,0)+isnull(b.BOMStock,0)+isnull(b.SubConStock,0)+isnull(b.InTransitStock,0)+isnull(b.ConsignmentStock,0) as TotalStock ")
        sql.AppendFormat(",isnull(cast(b.WHStock as varchar),'NA') as WHStock ")
        sql.AppendFormat(",isnull(cast(b.BOMStock as varchar),'NA') as BOMStock ")
        sql.AppendFormat(",isnull(cast(b.SubConStock as varchar),'NA') as SubConStock ")
        sql.AppendFormat(",isnull(cast(b.InTransitStock as varchar),'NA') as InTransitStock ")
        sql.AppendFormat(",isnull(cast(b.ConsignmentStock as varchar),'NA') as ConsignmentStock ")
        sql.AppendFormat(",isnull(cast(S.MTD_S as varchar),'NA') as MTD_S ")
        sql.AppendFormat(",isnull(cast(PBOM.MTD_S as varchar),'NA') as MTD_PBOM ")
        sql.AppendFormat("from ProductData a ")
        sql.AppendFormat("left join ProductStock b on a.Sapcode=b.Sapcode ")
        sql.AppendFormat("left join ")
        sql.AppendFormat("( ")
        sql.AppendFormat("  select a.Sapcode,sum(a.stock) as TradeStock from ProductTradeStock a ")
        sql.AppendFormat("  join channelview b on a.CustomerGroup=b.Channel ")
        sql.AppendFormat("  and b.Signature='{0}' ", Session(Me.SessionID & "SF_Signature"))
        sql.AppendFormat("  and a.CustomerGroup IN ({0}) ", MyObj.JoinFormat(",", "'{0}'", Me.ChlData))
        sql.AppendFormat("  group by a.Sapcode ")
        sql.AppendFormat(") ")
        sql.AppendFormat("c on a.Sapcode=c.Sapcode ")

        sql.AppendFormat("left join ")
        sql.AppendFormat("( ")
        sql.AppendFormat("  select a.BomSapcode as Sapcode,Cast(sum(isnull(b.stock,0)*isnull(a.QTY,0)) as numeric(18, 3)) as TradeStock from ProductBom{1} a ", DateTime.Today.Month, Me.MonthCode)
        sql.AppendFormat("  join ProductTradeStock b on a.SapCode=b.SapCode ", DateTime.Today.Year)
        sql.AppendFormat("  where 1=1 ")
        sql.AppendFormat("  and a.BomSapcode='{0}' ", Me.SapCode)
        sql.AppendFormat("  and b.CustomerGroup IN ({0}) ", MyObj.JoinFormat(",", "'{0}'", Me.ChlData))
        sql.AppendFormat("  group by a.BomSapcode ")
        sql.AppendFormat(") STOCK_PBOM on a.Sapcode=STOCK_PBOM.Sapcode ")

        sql.AppendFormat("left join ")
        sql.AppendFormat("( ")
        sql.AppendFormat("  select Sapcode,Cast(sum(isnull(Mon{0},0)) as numeric(18, 3)) as MTD_S from SFReport{1}A ", DateTime.Today.Month, DateTime.Today.Year)
        sql.AppendFormat("  where 1=1 ")
        sql.AppendFormat("  and Sapcode='{0}' ", Me.SapCode)
        sql.AppendFormat("  and Skutype='{0}' ", SkuType)
        sql.AppendFormat("  and ChannelID IN ({0}) ", MyObj.JoinFormat(",", "'{0}'", Me.ChlData))
        sql.AppendFormat("  group by Sapcode ")
        sql.AppendFormat(") S on a.Sapcode=S.Sapcode ")

        If SkuType = "S" Then
            sql.AppendFormat("left join ")
            sql.AppendFormat("( ")
            sql.AppendFormat("  select Sapcode,Cast(sum(isnull(Mon{0},0)) as numeric(18, 3)) as MTD_S from SFReport{1}A ", DateTime.Today.Month, DateTime.Today.Year)
            sql.AppendFormat("  where 1=1 ")
            sql.AppendFormat("  and Sapcode='{0}' ", Me.SapCode)
            sql.AppendFormat("  and Skutype='{0}' ", "PBOM")
            sql.AppendFormat("  and ChannelID IN ({0}) ", MyObj.JoinFormat(",", "'{0}'", Me.ChlData))
            sql.AppendFormat("  group by Sapcode ")
            sql.AppendFormat(") PBOM on a.Sapcode=PBOM.Sapcode ")
        Else
            sql.AppendFormat("left join ")
            sql.AppendFormat("( ")
            sql.AppendFormat("  select a.BomSapcode as Sapcode,Cast(sum(isnull(b.Mon{0},0)) as numeric(18, 3)) as MTD_S from ProductBom{1} a ", DateTime.Today.Month, Me.MonthCode)
            sql.AppendFormat("  join SFReport{0}A b on a.SapCode=b.SapCode ", DateTime.Today.Year)
            sql.AppendFormat("  where 1=1 ")
            sql.AppendFormat("  and a.BomSapcode='{0}' ", Me.SapCode)
            sql.AppendFormat("  and b.Skutype='{0}' ", SkuType)
            sql.AppendFormat("  and ChannelID IN ({0}) ", MyObj.JoinFormat(",", "'{0}'", Me.ChlData))
            sql.AppendFormat("  group by a.BomSapcode ")
            sql.AppendFormat(") PBOM on a.Sapcode=PBOM.Sapcode ")
        End If

        sql.AppendFormat("where a.Sapcode='{0}' ", Me.SapCode)
        'MyObj.WriteLog(sql.ToString())
        dt = dat.GetData(sql.ToString())
        dt = MyObj.RemoveDTZero(dt)

        Me.fv.DataSource = dt
        Me.fv.DataBind()
    End Sub

    Protected Sub fv_DataBound(ByVal sender As Object, ByVal e As System.EventArgs) Handles fv.DataBound
        If fv.DataMember IsNot Nothing Then
            'OldCode
            Me.OldCode = DataBinder.Eval(fv.DataItem, "OldCode").ToString()
            Dim hlinkOldCodeValue As HyperLink = TryCast(Me.fv.FindControl("hlinkOldCodeValue"), HyperLink)
            hlinkOldCodeValue.Text = Me.OldCode

            Dim sql As New StringBuilder
            Dim dat As New clsData
            Dim dt As DataTable

            sql.Length = 0
            sql.AppendFormat("select distinct a.ChannelName,isnull(cast(b.Stock as varchar),'NA') as Stock from ChannelView a ")
            sql.AppendFormat("left join ProductTradeStock b on a.Channel=b.CustomerGroup ")
            sql.AppendFormat("and b.SapCode='{0}' ", Me.SapCode)
            sql.AppendFormat("where a.Division='{0}' ", Session(Me.SessionID & "SF_Division"))
            sql.AppendFormat("and a.Signature='{0}' ", Session(Me.SessionID & "SF_Signature"))
            sql.AppendFormat("and a.Channel IN ({0}) ", MyObj.JoinFormat(",", "'{0}'", Me.ChlData))
            'MyObj.WriteLog(sql.ToString())
            dt = dat.GetData(sql.ToString())
            dt = MyObj.RemoveDTZero(dt)

            TryCast(Me.fv.FindControl("rptTradeStock"), DataList).DataSource = dt
            TryCast(Me.fv.FindControl("rptTradeStock"), DataList).DataBind()

            'MTD ALL
            Dim lblMTDValue_ALL As Label = TryCast(Me.fv.FindControl("lblMTDValue_ALL"), Label)
            Dim lblMTDValue_S As Label = TryCast(Me.fv.FindControl("lblMTDValue_S"), Label)
            Dim lblMTDValue_PBOM As Label = TryCast(Me.fv.FindControl("lblMTDValue_PBOM"), Label)
            If lblMTDValue_S.Text = "NA" And lblMTDValue_PBOM.Text = "NA" Then
                lblMTDValue_ALL.Text = "NA"
            Else
                Dim MTD_ALL As Integer = 0
                If IsNumeric(lblMTDValue_S.Text) Then
                    MTD_ALL += lblMTDValue_S.Text
                End If
                If IsNumeric(lblMTDValue_PBOM.Text) Then
                    MTD_ALL += lblMTDValue_PBOM.Text
                End If
                lblMTDValue_ALL.Text = MTD_ALL.ToString()
            End If

            'TotalTradeStock
            Dim lblTradeStockTotalValue As Label = TryCast(Me.fv.FindControl("lblTradeStockTotalValue"), Label)
            Dim lblTradeStock_PBOMValue As Label = TryCast(Me.fv.FindControl("lblTradeStock_PBOMValue"), Label)
            Dim lblTradeStockValue As Label = TryCast(Me.fv.FindControl("lblTradeStockValue"), Label)
            If lblTradeStock_PBOMValue.Text = "NA" And lblTradeStockValue.Text = "NA" Then
                lblTradeStockTotalValue.Text = "NA"
            Else
                Dim TradeStockTotal As Integer = 0
                If IsNumeric(lblTradeStock_PBOMValue.Text) Then
                    TradeStockTotal += lblTradeStock_PBOMValue.Text
                End If
                If IsNumeric(lblTradeStockValue.Text) Then
                    TradeStockTotal += lblTradeStockValue.Text
                End If
                lblTradeStockTotalValue.Text = TradeStockTotal.ToString("0.###")
            End If

            '計算Coverge Day
            '============================================================
            Dim mons As New List(Of String)
            For i As Integer = 1 To 36
                mons.Add(i)
            Next

            Dim dtFCST As New DataTable
            sql.Length = 0
            sql.AppendFormat("Select Sapcode,{0} ", MyObj.JoinFormat(",", "Sum(ISNULL(Mon{0},0)) as Mon{0}", mons))
            sql.AppendFormat("from {0}SFReport{1} sf ", Me.HeaderTB, Me.MonthCode)
            sql.AppendFormat("where 1=1 ")
            sql.AppendFormat("and Sapcode='{0}' ", Me.SapCode)
            Select Case Me.HeaderTB
                Case "SO_"
                    sql.AppendFormat("and Skutype='{0}' ", "SellOut")
                Case "PLV_"
                    sql.AppendFormat("and Skutype='{0}' ", "S")
                Case Else
                    sql.AppendFormat("and Skutype='{0}' ", "S")
            End Select
            sql.AppendFormat("and ChannelID IN ({0}) ", MyObj.JoinFormat(",", "'{0}'", Me.ChlData))
            sql.AppendFormat("group by Sapcode ")
            'MyObj.WriteLog(sql.ToString())
            dtFCST = dat.GetData(sql.ToString())
            dtFCST = MyObj.RemoveDTZero(dtFCST)

            Dim dtACT As New DataTable
            sql.Length = 0
            sql.AppendFormat("Select Sum(isnull(Mon{0},0)) as ACTValue ", DateTime.Now.Month)
            sql.AppendFormat("from SFReport{0}A sf ", Left(Me.MonthCode, 4))
            sql.AppendFormat("where 1=1 ")
            sql.AppendFormat("and Sapcode='{0}' ", Me.SapCode)
            Select Case Me.HeaderTB
                Case "SO_"
                    sql.AppendFormat("and Skutype='{0}' ", "SellOut")
                Case "PLV_"
                    sql.AppendFormat("and Skutype='{0}' ", "S")
                Case Else
                    sql.AppendFormat("and Skutype='{0}' ", "S")
            End Select
            sql.AppendFormat("and ChannelID IN ({0}) ", MyObj.JoinFormat(",", "'{0}'", Me.ChlData))
            sql.AppendFormat("group by Sapcode ")
            'MyObj.WriteLog(sql.ToString())
            dtACT = dat.GetData(sql.ToString())
            dtACT = MyObj.RemoveDTZero(dtACT)

            Dim lblStockCoverDaysValue As Label = TryCast(Me.fv.FindControl("lblStockCoverDaysValue"), Label)
            Dim ACTValue As Integer = 0
            If dtACT.Rows.Count <> 0 Then
                ACTValue = dtACT.Rows(0)("ACTValue").ToString()
            End If

            Dim CovergeDay As Integer = 0
            Dim CounterStock As Integer = 0
            '只有本月跟下月才會算
            If Me.MonthCode = DateTime.Now.ToString("yyyyMM") Or Me.MonthCode = DateTime.Now.AddMonths(1).ToString("yyyyMM") Then
                If IsNumeric(TryCast(Me.fv.FindControl("lblTradeStockValue"), Label).Text) Then
                    CounterStock = TryCast(Me.fv.FindControl("lblTradeStockValue"), Label).Text
                End If

                Dim MonthDay As Integer = 0
                Dim MyDate As Date
                Dim FCSTValue As Integer = 0
                For i As Integer = Me.FCSTMonth To 36
                    If dtFCST.Rows.Count <> 0 Then
                        FCSTValue = dtFCST.Rows(0)(String.Format("Mon{0}", i)).ToString()
                    Else
                        FCSTValue = 0
                    End If
                    If i = Me.FCSTMonth Then
                        MyDate = New Date(Left(Me.MonthCode, 4), i, 1)
                        FCSTValue = FCSTValue - ACTValue '第一個月份要先扣掉ACT的值
                        If Me.MonthCode = DateTime.Now.ToString("yyyyMM") Then
                            MonthDay = DateTime.DaysInMonth(Year(MyDate), Month(MyDate)) - Day(Now()) '取剩餘天數
                        Else
                            MonthDay = DateTime.DaysInMonth(Year(MyDate), Month(MyDate)) '當月全部天數
                        End If
                    Else
                        MyDate = MyDate.AddMonths(1)
                        MonthDay = DateTime.DaysInMonth(Year(MyDate), Month(MyDate))
                    End If

                    If CounterStock - FCSTValue > 0 Then
                        CounterStock -= FCSTValue 'CounterStock會越來越少
                        CovergeDay += MonthDay
                    Else
                        If FCSTValue * MonthDay <> 0 Then
                            CovergeDay += CounterStock / FCSTValue * MonthDay
                        End If
                        Exit For '離開迴圈
                    End If
                Next
            End If
            If CovergeDay > 999 Then
                CovergeDay = 999
            End If
            lblStockCoverDaysValue.Text = CovergeDay
            '============================================================

            '更改Text
            Dim lblMTD_ALL As Label = TryCast(Me.fv.FindControl("lblMTD_ALL"), Label)
            Dim lblMTD_PBOM As Label = TryCast(Me.fv.FindControl("lblMTD_PBOM"), Label)
            Dim lblMTD_S As Label = TryCast(Me.fv.FindControl("lblMTD_S"), Label)
            Select Case Me.HeaderTB
                Case "SO_"
                    lblMTD_ALL.Text = "Sell-Out(MTD)："
                    lblMTD_PBOM.Text = "Sell-Out-PBOM(MTD)："
                    lblMTD_S.Text = "Sell-Out-S(MTD)："
                Case "PLV_"
                    lblMTD_ALL.Text = "Issue Qty(MTD)："
                    lblMTD_PBOM.Text = "Issue Qty-PBOM(MTD)："
                    lblMTD_S.Text = "Issue Qty-S(MTD)："
                Case Else
                    lblMTD_ALL.Text = "Sell-in(MTD)："
                    lblMTD_PBOM.Text = "Sell-in-PBOM(MTD)："
                    lblMTD_S.Text = "Sell-in-S(MTD)："
            End Select
        End If
    End Sub

#Region "屬性"
    Private Property SapCode() As String
        Get
            Return ViewState("SapCode")
        End Get
        Set(ByVal value As String)
            ViewState("SapCode") = value
        End Set
    End Property

    Protected ReadOnly Property Signature() As String
        Get
            Return Session(Me.SessionID & "SF_Signature")
        End Get
    End Property

    Protected ReadOnly Property Channel() As String
        Get
            Return Session(Me.SessionID & "SF_Channel")
        End Get
    End Property

    Public ReadOnly Property SessionID() As String
        Get
            Return Me.Request.QueryString.Item("SessionID")
        End Get
    End Property

    Protected ReadOnly Property FCSTMonth() As String
        Get
            Return Session(Me.SessionID & "SF_FCSTMonth")
        End Get
    End Property

    Protected ReadOnly Property MonthCode() As String
        Get
            Return Session(Me.SessionID & "SF_MonthCode")
        End Get
    End Property

    Public ReadOnly Property HeaderTB() As String
        Get
            Return Session(Me.SessionID & "SF_HeaderTB")
        End Get
    End Property

    Public ReadOnly Property ViewPoint() As String
        Get
            Return Session(Me.SessionID & "SF_ViewPoint")
        End Get
    End Property

    Public Property OldCode() As String
        Get
            Return ViewState("OldCode")
        End Get
        Set(ByVal value As String)
            ViewState("OldCode") = value
        End Set
    End Property
#End Region

#Region "Group Property"
    Private _ChlData As List(Of String)
    Protected ReadOnly Property ChlData() As List(Of String)
        Get
            If Me._ChlData Is Nothing Then
                Me._ChlData = cmanager.GetFullBySignature(Me.Signature)
                'If Me.Channel = String.Empty Then
                '    Me._ChlData = Me.cmanager.GetGroupChannels(Me.Division, Me.Signature, MyObj.SelectAllValue)
                'Else
                '    Me._ChlData = Me.cmanager.GetGroupChannels(Me.Division, Me.Signature, Me.Channel)
                'End If
            End If
            Return Me._ChlData
        End Get
    End Property

    '判斷是否為All或Group Channel
    Protected ReadOnly Property IsMutiChannel() As Boolean
        Get
            If Me.Channel = String.Empty OrElse Me.Channel.IndexOf(MyObj.GroupMark) <> -1 Then
                Return True
            Else
                Return False
            End If
        End Get
    End Property
#End Region
End Class
